/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ru.ibrae.mchs.radiation.common.reports;

import java.util.logging.Level;
import java.util.logging.Logger;
import ru.ibrae.mchs.radiation.common.excelEntities.ExcelSelsovet;
import ru.ibrae.mchs.radiation.common.excelEntities.ExcelGeoobject;
import ru.ibrae.mchs.radiation.common.excelEntities.ExcelSubject;
import ru.ibrae.mchs.radiation.common.excelEntities.ExcelGSPos;
import ru.ibrae.mchs.radiation.common.excelEntities.ExcelOldRajon;
import ru.ibrae.mchs.radiation.common.excelEntities.ExcelRajon;
import ru.ibrae.mchs.radiation.common.CityCarrier;
import ru.ibrae.mchs.radiation.common.excelEntities.zonage.ExcelSubjectZonage;
import ru.ibrae.mchs.radiation.common.PokazatelMap;
import ru.ibrae.mchs.radiation.entities.Geoobject;
import ru.ibrae.mchs.radiation.entities.SubjectRf;
import ru.ibrae.mchs.radiation.entities.controllers.CrossZonJpaController;
import ru.ibrae.mchs.radiation.entities.controllers.DicTimeJpaController;
import ru.ibrae.mchs.radiation.entities.controllers.DicZonJpaController;
import ru.ibrae.mchs.radiation.entities.controllers.GeoobjectJpaController;
import ru.ibrae.mchs.radiation.entities.controllers.PokazatelJpaController;
import ru.ibrae.mchs.radiation.entities.controllers.ResultJpaController;
import ru.ibrae.mchs.radiation.entities.controllers.SubjectRfJpaController;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.filechooser.FileNameExtensionFilter;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.jdesktop.swingx.JXLabel;

/**
 *
 * @author murr
 */
public class ReportsCreator {

    public static void createReport(ReportType report) throws IOException, WriteException, BiffException, Exception {
        PokazatelJpaController pjc = new PokazatelJpaController();
        ResultJpaController rjc = new ResultJpaController();
        DicTimeJpaController dtjc = new DicTimeJpaController();
        DicZonJpaController dzjc = new DicZonJpaController();
        CrossZonJpaController czjc = new CrossZonJpaController();
        GeoobjectJpaController gjc = new GeoobjectJpaController();
        SubjectRfJpaController srjc = new SubjectRfJpaController();
        PokazatelMap pokazatelMap2 = new PokazatelMap(pjc.findPokazatel(2), dtjc.findDicTime(rjc.getMaxDicTimeByIdP(pjc.findPokazatel(2).getIdP())));
        PokazatelMap pokazatelMap8 = new PokazatelMap(pjc.findPokazatel(8), dtjc.findDicTime(rjc.getMaxDicTimeByIdP(pjc.findPokazatel(8).getIdP())));
        PokazatelMap pokazatelMap17 = new PokazatelMap(pjc.findPokazatel(17), dtjc.findDicTime(rjc.getMaxDicTimeByIdP(pjc.findPokazatel(17).getIdP())));
        PokazatelMap pokazatelMap21 = new PokazatelMap(pjc.findPokazatel(21), dtjc.findDicTime(rjc.getMaxDicTimeByIdP(pjc.findPokazatel(21).getIdP())));
        PokazatelMap[] pokazatelMaps = new PokazatelMap[]{pokazatelMap2, pokazatelMap8, pokazatelMap17, pokazatelMap21};
        ArrayList<Geoobject> usedGeoobjects = new ArrayList<Geoobject>();
        JFileChooser chooser = new JFileChooser(new File("./reports/"));
        chooser.setFileFilter(new FileNameExtensionFilter("Файлы Microsoft Excel 97-2003", ".xls"));
        int showSaveDialog = chooser.showSaveDialog(null);
        File fileToChange = chooser.getSelectedFile();
        if (!fileToChange.getName().contains(".xls")) {
            fileToChange = new File(fileToChange.getAbsolutePath() + ".xls");
        }
        boolean createNewFile = fileToChange.createNewFile();
        WritableCellFormat brushFormat = new WritableCellFormat();
        brushFormat.setBackground(Colour.LIGHT_GREEN);
        if (report == ReportType.FULL) {
            CityCarrier[] cityCarriers = getPlannedCityCarriers(pokazatelMaps);
            createEnumeration(fileToChange, 0, pokazatelMaps, cityCarriers);
        }// </editor-fold>
        else if (report.toString().startsWith("ZONAGE")) {
            PokazatelMap pokazatelMap1 = new PokazatelMap(pjc.findPokazatel(1), dtjc.findDicTime(dtjc.getNaselenMaxTime()));
            CityCarrier[] cityCarriers = report.toString().startsWith("ZONAGE_P") ? getPlannedCityCarriers(pokazatelMaps) : getActiveCityCarriers();
            ArrayList<ExcelSubjectZonage> subjects = new ArrayList<ExcelSubjectZonage>();
            for (SubjectRf subjectRf : srjc.findSubjectRfEntities()) {
                boolean add = subjects.add(new ExcelSubjectZonage(subjectRf.getName1()));
            }
            Collections.sort(subjects, new Comparator<ExcelSubjectZonage>() {

                @Override
                public int compare(ExcelSubjectZonage o1, ExcelSubjectZonage o2) {
                    return o1.getSubjectRf().compareToIgnoreCase(o2.getSubjectRf());
                }
            });
            Workbook fileToCopyFrom = Workbook.getWorkbook(report.getTemplate());
            WritableWorkbook workbook = Workbook.createWorkbook(fileToChange, fileToCopyFrom);
            WritableSheet sheet = workbook.getSheet(0);
            Integer currentRow = 5;
            switch (report) {
                case ZONAGE_ACTIVE_ALL:
                case ZONAGE_PLANNED_ALL:
                    for (int i = 0; i < cityCarriers.length; i++) {
                        CityCarrier cityCarrier = cityCarriers[i];
                        boolean addAll = cityCarrier.getBasicGeoobjects().addAll(cityCarrier.getAddedGeoobjects());
                        for (Geoobject geoobject : cityCarrier.getBasicGeoobjects()) {
                            for (ExcelSubjectZonage excelSubjectZonage : subjects) {
                                if (excelSubjectZonage.getSubjectRf().equals(geoobject.getSubjectRf().getName1())) {
                                    if (!usedGeoobjects.contains(geoobject)) {
                                        excelSubjectZonage.getCitiesListByZoneId(i + 1).add(geoobject.getIdNp());
                                    }
                                    break;
                                }
                            }
                        }
                    }
                    for (ExcelSubjectZonage excelSubjectZonage : subjects) {

                        WritableCell subjectName = new Label(0, currentRow, excelSubjectZonage.getSubjectRf().trim());
                        WritableCell commonCitiesCount = excelSubjectZonage.getTotalCitiesNumber() == null ? new Label(1, currentRow, "") : new Number(1, currentRow, excelSubjectZonage.getTotalCitiesNumber());
                        WritableCell commonPopulation = excelSubjectZonage.getTotalPopul(pokazatelMap1) == null ? new Label(2, currentRow, "") : new Number(2, currentRow, getRoundedValue(excelSubjectZonage.getTotalPopul(pokazatelMap1) / 1000));
                        WritableCell[] others = new WritableCell[8];
                        for (int i = 3; i < others.length + 3; i++) {
                            int zone = 0;
                            int curCol = i % 2 == 0 ? 14 - i : 12 - i;
                            switch (i) {
                                case 3:
                                case 4:
                                    zone = 1;
                                    break;
                                case 5:
                                case 6:
                                    zone = 2;
                                    break;
                                case 7:
                                case 8:
                                    zone = 3;
                                    break;
                                case 9:
                                case 10:
                                    zone = 4;
                                    break;
                            }
                            if (i % 2 == 1) {
                                others[i - 3] = excelSubjectZonage.getCommonCititesNumberByZone(zone) == null ? new Label(curCol, currentRow, "") : new Number(curCol, currentRow, excelSubjectZonage.getCommonCititesNumberByZone(zone));
                            } else {
                                others[i - 3] = excelSubjectZonage.getCommonPopulByZone(zone, pokazatelMap1) == null ? new Label(curCol, currentRow, "") : new Number(curCol, currentRow, getRoundedValue(excelSubjectZonage.getCommonPopulByZone(zone, pokazatelMap1) / 1000));
                            }

                        }
                        sheet.addCell(subjectName);
                        sheet.addCell(commonCitiesCount);
                        sheet.addCell(commonPopulation);
                        for (WritableCell writableCell : others) {
                            sheet.addCell(writableCell);
                        }
                        currentRow++;
                    }
                    break;
                case ZONAGE_ACTIVE_NP:
                case ZONAGE_PLANNED_NP:
                    for (int i = 0; i < cityCarriers.length; i++) {
                        CityCarrier cityCarrier = cityCarriers[i];
                        boolean addAll = cityCarrier.getBasicGeoobjects().addAll(cityCarrier.getAddedGeoobjects());
                        for (Geoobject geoobject : cityCarrier.getBasicGeoobjects()) {
                            for (ExcelSubjectZonage excelSubjectZonage : subjects) {
                                if (excelSubjectZonage.getSubjectRf().equals(geoobject.getSubjectRf().getName1())) {
                                    if (!usedGeoobjects.contains(geoobject)) {
                                        excelSubjectZonage.getCitiesListByZoneId(i + 1).add(geoobject.getIdNp());
                                    }
                                    break;
                                }
                            }
                        }
                    }
                    for (ExcelSubjectZonage excelSubjectZonage : subjects) {
                        WritableCell subjectName = new Label(0, currentRow, excelSubjectZonage.getSubjectRf().trim());
                        WritableCell commonCitiesCount = excelSubjectZonage.getTotalActiveCitiesNumber() == null ? new Label(1, currentRow, "") : new Number(1, currentRow, excelSubjectZonage.getTotalActiveCitiesNumber());
                        WritableCell commonPopulation = excelSubjectZonage.getTotalActivePopul(pokazatelMap1) == null ? new Label(2, currentRow, "") : new Number(2, currentRow, getRoundedValue(excelSubjectZonage.getTotalActivePopul(pokazatelMap1) / 1000));
                        WritableCell[] others = new WritableCell[8];
                        for (int i = 3; i < others.length + 3; i++) {
                            Integer zone = null;
                            int curCol = i % 2 == 0 ? 14 - i : 12 - i;
                            switch (i) {
                                case 3:
                                case 4:
                                    zone = 1;
                                    break;
                                case 5:
                                case 6:
                                    zone = 2;
                                    break;
                                case 7:
                                case 8:
                                    zone = 3;
                                    break;
                                case 9:
                                case 10:
                                    zone = 4;
                                    break;
                            }
                            if (i % 2 == 1) {
                                others[i - 3] = excelSubjectZonage.getActiveCititesNumberByZone(zone) == null ? new Label(curCol, currentRow, "") : new Number(curCol, currentRow, excelSubjectZonage.getActiveCititesNumberByZone(zone));
                            } else {
                                others[i - 3] = excelSubjectZonage.getActivePopulByZone(zone, pokazatelMap1) == null ? new Label(curCol, currentRow, "") : new Number(curCol, currentRow, getRoundedValue(excelSubjectZonage.getActivePopulByZone(zone, pokazatelMap1) / 1000));
                            }
                        }
                        sheet.addCell(subjectName);
                        sheet.addCell(commonCitiesCount);
                        sheet.addCell(commonPopulation);
                        for (WritableCell writableCell : others) {
                            sheet.addCell(writableCell);
                        }
                        currentRow++;
                    }
                    break;
                case ZONAGE_ACTIVE_REMOVED:
                case ZONAGE_PLANNED_REMOVED:
                    for (int i = 0; i < cityCarriers.length; i++) {
                        CityCarrier cityCarrier = cityCarriers[i];
                        boolean addAll = cityCarrier.getBasicGeoobjects().addAll(cityCarrier.getAddedGeoobjects());
                        for (Geoobject geoobject : cityCarrier.getBasicGeoobjects()) {
                            for (ExcelSubjectZonage excelSubjectZonage : subjects) {
                                if (excelSubjectZonage.getSubjectRf().equals(geoobject.getSubjectRf().getName1())) {
                                    if (!usedGeoobjects.contains(geoobject)) {
                                        excelSubjectZonage.getCitiesListByZoneId(i + 1).add(geoobject.getIdNp());
                                    }
                                    break;
                                }
                            }
                        }
                    }
                    for (ExcelSubjectZonage excelSubjectZonage : subjects) {
                        WritableCell subjectName = new Label(0, currentRow, excelSubjectZonage.getSubjectRf().trim());
                        WritableCell commonCitiesCount = excelSubjectZonage.getTotalRemovedCitiesNumber() == null ? new Label(1, currentRow, "") : new Number(1, currentRow, excelSubjectZonage.getTotalRemovedCitiesNumber());
                        WritableCell[] others = new WritableCell[4];
                        for (int i = 2; i < others.length + 2; i++) {
                            int zone = 6 - i;
                            int curCol = i;
                            others[i - 2] = excelSubjectZonage.getRemovedCititesNumberByZone(zone) == null ? new Label(curCol, currentRow, "") : new Number(curCol, currentRow, excelSubjectZonage.getRemovedCititesNumberByZone(zone));
                        }
                        sheet.addCell(subjectName);
                        sheet.addCell(commonCitiesCount);
                        for (WritableCell writableCell : others) {
                            sheet.addCell(writableCell);
                        }
                        currentRow++;
                    }
                    break;
                default:
                    throw new Exception("irreal exception");
            }
            for (int checkRow = 5; checkRow < 29; checkRow++) {
                Boolean empty = true;
                for (int checkCol = 1; checkCol < sheet.getColumns(); checkCol++) {
                    if (sheet.getCell(checkCol, checkRow) instanceof Number) {
                        empty = false;
                        break;
                    }
                }
                if (empty) {
                    for (int checkCol = 0; checkCol < sheet.getColumns(); checkCol++) {
                        WritableCell cellToColour = sheet.getWritableCell(checkCol, checkRow);
                        cellToColour.setCellFormat(brushFormat);
                    }
                }
            }
            workbook.write();
            workbook.close();
            JOptionPane.showMessageDialog(null, "Отчет \"" + report.getDescription() + "\" составлен. \nСохранен в файл: " + fileToChange.getAbsolutePath());
            currentRow = 5;
            createEnumeration(fileToChange, 1, pokazatelMaps, cityCarriers);
        }
    }

    // <editor-fold defaultstate="collapsed" desc="func to load any zone (1-4)">
    private static ArrayList<Integer> loadZone(int i, List<Integer> exclude, PokazatelMap... pokazatelMaps) {
        ResultJpaController rjc = new ResultJpaController();
        CrossZonJpaController czjc = new CrossZonJpaController();
        switch (i) {
            case 1:
                return new ArrayList<Integer>(czjc.findActiveGeoobjIdsWithZone1());
            case 2:
                return rjc.getGeoobjectsByZone(i, exclude, pokazatelMaps);
            case 3:
                return rjc.getGeoobjectsByZone(i, exclude, pokazatelMaps);
            case 4:
                return rjc.getGeoobjectsByZone(i, exclude, pokazatelMaps);
        }
        return null;
    }// </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="static func to get all city carriers for any report">
    private static CityCarrier[] getPlannedCityCarriers(PokazatelMap[] pokazatelMaps) {
        GeoobjectJpaController gjc = new GeoobjectJpaController();
        ResultJpaController rjc = new ResultJpaController();
        DicZonJpaController dzjc = new DicZonJpaController();
        CityCarrier[] cityCarriers = new CityCarrier[4];
        for (int i = 0; i < 4; i++) {
            cityCarriers[i] = new CityCarrier(i + 1);
        }
        ArrayList<Integer> additionalExcluded = new ArrayList<Integer>();
        ArrayList<Integer> zone1 = new ArrayList<Integer>();
        ArrayList<Integer> zone2 = new ArrayList<Integer>();
        ArrayList<Integer> zone3 = new ArrayList<Integer>();
        ArrayList<Integer> zone4 = new ArrayList<Integer>();
        ArrayList<Integer>[] zones = new ArrayList[]{zone1, zone2, zone3, zone4};
        for (int i = 1; i < 5; i++) {
            switch (i) {
                case 1:
                    ArrayList<Integer> temp = loadZone(i, null, pokazatelMaps);
                    for (int j = 0; j < temp.size(); j++) {
                        Integer id = temp.get(j);
                        Geoobject g = gjc.findGeoobject(id);
                        Integer zone = classify(g, 0, pokazatelMaps);
                        if (zone == 0) {
                            zone1.add(id);
                        } else {
                            String zonName = dzjc.findDicZon(zone + 1).getStatusZon();
                            if (JOptionPane.showConfirmDialog(null,
                                    "Есть возможность переместить населенный  пункт \"" + g.getNameNp() + "\" в зону \n"
                                    + "\"" + zonName + "\".\n"
                                    + "Переместить?",
                                    "Перемещение населенного пункта", JOptionPane.YES_NO_OPTION) == JOptionPane.YES_OPTION) {
                                cityCarriers[zone].getAddedGeoobjects().add(g);
                                additionalExcluded.add(id);
                            } else {
                                zone1.add(id);
                            }
                        }
                    }
                    break;
                case 2:
                    ArrayList<Integer> integers2 = new ArrayList<Integer>();
                    integers2.addAll(additionalExcluded);
                    integers2.addAll(zones[0]);
                    zones[i - 1] = loadZone(i, integers2, pokazatelMaps);
                    break;
                case 3:
                    ArrayList<Integer> integers = new ArrayList<Integer>();
                    integers.addAll(zones[0]);
                    integers.addAll(zones[1]);
                    integers.addAll(additionalExcluded);
                    zones[i - 1] = loadZone(i, integers, pokazatelMaps);
                    break;
                case 4:
                    ArrayList<Integer> integers1 = new ArrayList<Integer>();
                    integers1.addAll(zones[0]);
                    integers1.addAll(zones[1]);
                    integers1.addAll(zones[2]);
                    integers1.addAll(additionalExcluded);
                    zones[i - 1] = loadZone(i, integers1, pokazatelMaps);
                    break;
            }
        }

        for (int i = 0; i < zones.length; i++) {
            ArrayList<Integer> geoIds = zones[i];
            for (Integer geoId : geoIds) {
                boolean add = cityCarriers[i].getBasicGeoobjects().add(gjc.findGeoobject(geoId));
            }
        }
        return cityCarriers;
    }// </editor-fold>

    private static CityCarrier[] getActiveCityCarriers() {
        CrossZonJpaController czjc = new CrossZonJpaController();
        GeoobjectJpaController gjc = new GeoobjectJpaController();
        CityCarrier[] carriers = new CityCarrier[4];
        for (int i = 0; i < carriers.length; i++) {
            carriers[i] = new CityCarrier(i + 1);
        }
        for (int i = 0; i < carriers.length; i++) {
            CityCarrier cityCarrier = carriers[i];
            ArrayList<Integer> geoIds = czjc.getActualCitiesByZone(i + 1);
            ArrayList<Integer> realGeoIds = geoIds == null ? new ArrayList<Integer>() : geoIds;
            for (Integer geoId : realGeoIds) {
                cityCarrier.getBasicGeoobjects().add(gjc.findGeoobject(geoId));
            }
        }
        return carriers;
    }

    // <editor-fold defaultstate="collapsed" desc="geoobject classifier">
    private static int classify(Geoobject geoobject, int currentCityCarrierIndex, PokazatelMap[] pokazatelMaps) {
        ResultJpaController rjc = new ResultJpaController();
        String[] reses = new String[4];
        for (int i = 0; i < pokazatelMaps.length; i++) {
            PokazatelMap pokazatelMap = pokazatelMaps[i];
            reses[i] = rjc.getResByIdPIdGeoobjeIdTime(pokazatelMap.getPokazatel().getIdP(), geoobject.getIdNp(), pokazatelMap.getTime().getIdTime());
        }
        for (int i = 0; i < reses.length; i++) {
            String string = reses[i];
            reses[i] = reses[i].replace("<", "");
            reses[i] = reses[i].replace(">", "");
            reses[i] = reses[i].trim();
        }
        String res2 = reses[0];
        String res8 = reses[1];
        String res17 = reses[2];
        String res21 = reses[3];
        if (!res8.equals("") && Double.parseDouble(res8) > 40) {
            if (currentCityCarrierIndex == 0) {
                return 0;
            } else {
                return 1;
            }
        } else if (res2.equals("") && res8.equals("") && res17.equals("") && res21.equals("")) {
            return -1;
        } else if ((!res2.equals("") && Double.parseDouble(res2) > 5) || (!res8.equals("") && Double.parseDouble(res8) > 15) || (!res21.equals("") && Double.parseDouble(res21) > 0.1) || (!res17.equals("") && Double.parseDouble(res17) > 3)) {
            return 1;
        } else if ((!res2.equals("") && Double.parseDouble(res2) > 1) || (!res8.equals("") && Double.parseDouble(res8) >= 5) && Double.parseDouble(res8) <= 15) {
            return 2;
        } else if ((!res8.equals("") && Double.parseDouble(res8) < 5 && Double.parseDouble(res8) > 1)) {
            return 3;
        } else {
            return -1;
        }
    }// </editor-fold>

    private static Double getRoundedValue(double d) {
        return new BigDecimal(d).setScale(2, RoundingMode.HALF_UP).doubleValue();
    }

    private static void createEnumeration(File fileToChange, int curSheet, PokazatelMap[] pokazatelMaps, CityCarrier[] cityCarriers) {
        try {
            // <editor-fold defaultstate="collapsed" desc="creation of full report (Перечень)">
            DicZonJpaController dzjc = new DicZonJpaController();
            File f = fileToChange;
            Workbook w = Workbook.getWorkbook(fileToChange);
            WritableWorkbook workbook = Workbook.createWorkbook(f, w);

            Integer currentSheet = curSheet;
            ArrayList<Geoobject> usedGeoobjects = new ArrayList<Geoobject>();
            WritableCellFormat subjectFormat = new WritableCellFormat(new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD, true));
            WritableCellFormat rajonFormat = new WritableCellFormat(new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD, true));
            WritableCellFormat gsposFormat = new WritableCellFormat(new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, true));
            for (CityCarrier cityCarrier : cityCarriers) {
                String sheetName = "";
                String fullNameOfSheet = dzjc.findDicZon(cityCarrier.getIdZon()).getStatusZon();
                if (fullNameOfSheet.equalsIgnoreCase("Зона отчуждения")) {
                    sheetName = "Зона отчужд.";
                } else if (fullNameOfSheet.equalsIgnoreCase("Зона отселения")) {
                    sheetName = "Зона отсел.";
                } else if (fullNameOfSheet.equalsIgnoreCase("Зона проживания с правом на отселение")) {
                    sheetName = "ЗППО";
                } else {
                    sheetName = "ЗПЛСС";
                }
                WritableSheet alive = workbook.createSheet(sheetName + " - существующие", currentSheet);
                currentSheet++;
                WritableSheet dead = workbook.createSheet(sheetName + " - упраздненные", currentSheet);
                currentSheet++;
                boolean addAll = cityCarrier.getBasicGeoobjects().addAll(cityCarrier.getAddedGeoobjects());
                ArrayList<Geoobject> fullList = cityCarrier.getBasicGeoobjects();
                ArrayList<Geoobject> deadList = new ArrayList<Geoobject>();
                ArrayList<Geoobject> aliveList = new ArrayList<Geoobject>();
                for (Geoobject geoobject : fullList) {
                    if (!usedGeoobjects.contains(geoobject)) {
                        boolean added = usedGeoobjects.add(geoobject);
                        if (!(geoobject.getComment() != null && geoobject.getComment().getComentary().startsWith("Включен"))) {
                            if (geoobject.getRajon() != null) {
                                aliveList.add(geoobject);
                            } else {
                                deadList.add(geoobject);
                            }
                        }
                    }
                }
                ArrayList<ExcelSubject> aliveSubjects = new ArrayList<ExcelSubject>();
                ArrayList<ExcelSubject> deadSubjects = new ArrayList<ExcelSubject>();
                if (!aliveList.isEmpty()) {
                    for (Geoobject geoobject : aliveList) {
                        /*Excel values pregeneration-----------------------------*/
                        String fullName = (geoobject.getTipNp() == null || geoobject.getTipNp().equals("")) ? geoobject.getNameNp() : geoobject.getTipNp() + ". " + geoobject.getNameNp();
                        ExcelGeoobject eg = new ExcelGeoobject(fullName);
                        ExcelGSPos egsp = geoobject.getGSPos() == null ? null : new ExcelGSPos(geoobject.getGSPos().getName2(), geoobject.getGSPos().getNumberPp());
                        ExcelRajon er = new ExcelRajon(geoobject.getRajon().getName2(), geoobject.getRajon().getNumberPp());
                        if (egsp == null) {
                            er.getExcelGeoobject().add(eg);
                        } else {
                            egsp.getExcelGeoobject().add(eg);
                            er.getExcelGSPos().add(egsp);
                        }
                        ExcelSubject es = new ExcelSubject(geoobject.getSubjectRf().getName1());
                        es.getRajons().add(er);
                        /*-------------------------------------------------------*/
                        if (aliveSubjects.contains(es)) {
                            for (ExcelSubject excelSubject : aliveSubjects) {
                                if (excelSubject.getName().equals(es.getName())) {
                                    if (excelSubject.getRajons().contains(er)) {
                                        for (ExcelRajon excelRajon : excelSubject.getRajons()) {
                                            if (excelRajon.getName().equalsIgnoreCase(er.getName())) {
                                                if (egsp == null) {
                                                    excelRajon.getExcelGeoobject().add(eg);
                                                } else {
                                                    if (excelRajon.getExcelGSPos().contains(egsp)) {
                                                        for (ExcelGSPos excelGSPos : excelRajon.getExcelGSPos()) {
                                                            if (excelGSPos.getName().equalsIgnoreCase(egsp.getName())) {
                                                                excelGSPos.getExcelGeoobject().add(eg);
                                                            }
                                                        }
                                                    } else {
                                                        excelRajon.getExcelGSPos().add(egsp);
                                                    }
                                                }
                                            }
                                        }
                                    } else {
                                        excelSubject.getRajons().add(er);
                                    }
                                }
                            }
                        } else {
                            aliveSubjects.add(es);
                        }
                    }
                }
                Collections.sort(aliveSubjects, new Comparator<ExcelSubject>() {

                    @Override
                    public int compare(ExcelSubject o1, ExcelSubject o2) {
                        return o1.getName().compareToIgnoreCase(o2.getName());
                    }
                });
                for (ExcelSubject excelSubject : aliveSubjects) {
                    excelSubject.doDeepSort();
                }
                if (!deadList.isEmpty()) {
                    for (Geoobject geoobject : deadList) {
                        /*Excel values pregeneration-----------------------------*/
                        String fullName = (geoobject.getTipNp() == null || geoobject.getTipNp().equals("")) ? geoobject.getNameNp() : geoobject.getTipNp() + ". " + geoobject.getNameNp();
                        ExcelGeoobject eg = new ExcelGeoobject(fullName);
                        ExcelSelsovet es = geoobject.getSelsovet() == null ? null : new ExcelSelsovet(geoobject.getSelsovet().getPlacename(), geoobject.getSelsovet().getNumberPp());
                        ExcelOldRajon eor = new ExcelOldRajon(geoobject.getRajonOld().getPlacename());
                        if (es == null) {
                            eor.getExcelGeoobjects().add(eg);
                        } else {
                            es.getExcelGeoobjects().add(eg);
                            eor.getExcelSelsovet().add(es);
                        }
                        ExcelSubject es1 = new ExcelSubject(geoobject.getSubjectRf().getName1());
                        es1.getOldRajons().add(eor);
                        /*-------------------------------------------------------*/
                        if (deadSubjects.contains(es1)) {
                            for (ExcelSubject excelSubject : deadSubjects) {
                                if (excelSubject.getName().equals(es1.getName())) {
                                    if (excelSubject.getOldRajons().contains(eor)) {
                                        for (ExcelOldRajon excelOldRajon : excelSubject.getOldRajons()) {
                                            if (excelOldRajon.getName().equalsIgnoreCase(eor.getName())) {
                                                if (es == null) {
                                                    excelOldRajon.getExcelGeoobjects().add(eg);
                                                } else {
                                                    if (excelOldRajon.getExcelSelsovet().contains(es)) {
                                                        for (ExcelSelsovet excelSelsovet : excelOldRajon.getExcelSelsovet()) {
                                                            if (excelSelsovet.getName().equalsIgnoreCase(es.getName())) {
                                                                excelSelsovet.getExcelGeoobjects().add(eg);
                                                            }
                                                        }
                                                    } else {
                                                        excelOldRajon.getExcelSelsovet().add(es);
                                                    }
                                                }
                                            }
                                        }
                                    } else {
                                        excelSubject.getOldRajons().add(eor);
                                    }
                                }
                            }
                        } else {
                            deadSubjects.add(es1);
                        }
                    }
                }
                Collections.sort(deadSubjects, new Comparator<ExcelSubject>() {

                    @Override
                    public int compare(ExcelSubject o1, ExcelSubject o2) {
                        return o1.getName().compareToIgnoreCase(o2.getName());
                    }
                });
                for (ExcelSubject excelSubject : deadSubjects) {
                    excelSubject.doDeepSort();
                }
                int currentRow = 0;
                for (ExcelSubject excelSubject : aliveSubjects) {
                    Label subject = new Label(0, currentRow, "                      " + excelSubject.getName(), subjectFormat);
                    alive.addCell(subject);
                    currentRow++;
                    for (ExcelRajon excelRajon : excelSubject.getRajons()) {
                        Label rajon = new Label(0, currentRow, "                " + excelRajon.getName(), rajonFormat);
                        alive.addCell(rajon);
                        currentRow++;
                        for (ExcelGeoobject excelGeoobject : excelRajon.getExcelGeoobject()) {
                            Label geoobjectBasic = new Label(0, currentRow, excelGeoobject.getName());
                            alive.addCell(geoobjectBasic);
                            currentRow++;
                        }
                        for (ExcelGSPos excelGSPos : excelRajon.getExcelGSPos()) {
                            Label gspos = new Label(0, currentRow, "        " + excelGSPos.getName(), gsposFormat);
                            alive.addCell(gspos);
                            currentRow++;
                            for (ExcelGeoobject excelGeoobject : excelGSPos.getExcelGeoobject()) {
                                Label geoobjectGSPosed = new Label(0, currentRow, excelGeoobject.getName());
                                alive.addCell(geoobjectGSPosed);
                                currentRow++;
                            }
                        }
                    }
                }
                currentRow = 0;
                for (ExcelSubject excelSubject : deadSubjects) {
                    Label subject = new Label(0, currentRow, "                      " + excelSubject.getName(), subjectFormat);
                    dead.addCell(subject);
                    currentRow++;
                    for (ExcelOldRajon excelOldRajon : excelSubject.getOldRajons()) {
                        Label rajon = new Label(0, currentRow, "                " + excelOldRajon.getName(), rajonFormat);
                        dead.addCell(rajon);
                        currentRow++;
                        for (ExcelGeoobject excelGeoobject : excelOldRajon.getExcelGeoobjects()) {
                            Label geoobjectBasic = new Label(0, currentRow, excelGeoobject.getName());
                            dead.addCell(geoobjectBasic);
                            currentRow++;
                        }
                        for (ExcelSelsovet excelSelsovet : excelOldRajon.getExcelSelsovet()) {
                            Label gspos = new Label(0, currentRow, "        " + excelSelsovet.getName(), gsposFormat);
                            dead.addCell(gspos);
                            currentRow++;
                            for (ExcelGeoobject excelGeoobject : excelSelsovet.getExcelGeoobjects()) {
                                Label geoobjectGSPosed = new Label(0, currentRow, excelGeoobject.getName());
                                dead.addCell(geoobjectGSPosed);
                                currentRow++;
                            }
                        }
                    }
                }
            }
            workbook.write();
            workbook.close();
            if (curSheet == 0) {
                JOptionPane.showMessageDialog(null, new JXLabel("Отчет сгенерирован в файле " + f.getAbsolutePath()));
            }
        } catch (BiffException ex) {
            Logger.getLogger(ReportsCreator.class.getName()).log(Level.SEVERE, null, ex);
        } catch (WriteException ex) {
            Logger.getLogger(ReportsCreator.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportsCreator.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
